<?php
namespace app\api\model;
use app\api\model\BrandModel;
use think\Db;
use app\common\errcode\ApiErrMsg;
class TaskModel extends \app\common\model\CommonModel
{
    /**
     * 添加任务
     * @param string $userid
     * @param string $departmentId
     * @param array  $data
     * 
     * @return bool
     */    
    public function addTask($userid, $departmentId, $data)
    {
        try {
            $tid = generateIdByPrefix('TID');
            $task_data = [
                'tid'                   => $tid,
                'name'                  => $data['name'],    
                'finish_date'           => $data['finish_date'],    
                'important'             => $data['important'],    
                'brand_id'              => $data['brand_id'],    
                'product_id'            => $data['product_id'],    
                'create_userid'         => $userid,    
                'charge_userid'         => $data['charge_userid'],    
                'charge_departmentId'   => $data['charge_departmentId'],
                'status'                => 1,
                'explain'               => !empty($data['explain']) ? $data['explain'] : '',    
                'remark'                => !empty($data['remark']) ? $data['remark'] : '',
                'latest_update'         => date('Y-m-d H:i:s'),    
            ];
            $task_list = [];
            foreach ($data['tasklist'] as $k => $v) {
                $task_list[$k]['tid']   = $tid;
                $task_list[$k]['type']  = $v['type'];
                $task_list[$k]['total'] = $v['total'];
            }
            $task_file = [];
            foreach ($data['filelist'] as $k => $v) {
                $task_file[$k]['tid']       = $tid;
                $task_file[$k]['name']      = $v['name'];
                $task_file[$k]['size']      = $v['size'];   
                $task_file[$k]['format']    = $v['format'];   
                $task_file[$k]['url']       = $v['url'];   
            }
            Db::startTrans();
            
            Db::table('task')->insert($task_data);    
            Db::table('task_list')->insertAll($task_list);    
            Db::table('task_file')->insertAll($task_file);
            // 写入日志
            $this->write_task_log($tid, $userid, "新增需求：【".$data['name']."】");   
            // 给负责人发送邮件通知
            $emailObj = new \app\common\tool\Email;
            $charge_userinfo = Db::table('enum_user')->field('email,username')->where(['userid' => $data['charge_userid']])->find();
            $create_userinfo = Db::table('enum_user')->field('email,username')->where(['userid' => $userid])->find();
            if (empty($charge_userinfo['email'])) {
                throw new \Exception('负责人邮箱未设置');
            }
            $info = [
                'to_email'          => $charge_userinfo['email'],
                'to_username'       => $charge_userinfo['username'],
                'task_name'         => $data['name'],
                'create_username'   => $create_userinfo['username'],
                'create_time'       => date('Y-m-d H:i:s'),
            ];
            $result = $emailObj->handle_send('TASK_IS_WAITING_ACCEPT', $info);
            if($result){
                Db::commit();
                return true;
            }
            Db::rollback();
            return false;   
        } catch (\Exception $e) {
            Db::rollback();
            throw new \Exception($e->getMessage());
        }
    }

    /**
     * 任务列表
     * @param int       $pagesize
     * @param int       $page
     * @param string    $create_userid
     * @param string    $type
     * @param int       $status
     * @param int       $important
     * @param string    $finish_date_start
     * @param string    $finish_date_end
     * @param int       $customer_id
     * @param int       $brand_id
     * @param int       $product_id
     * 
     * @return array
     */
    public function getList($userid, $departmentId, $params)
    {
        try {
            $page     = isset($params['page']) && is_numeric($params['page']) ? (int)$params['page'] : 1;
            $pagesize = isset($params['pagesize']) && is_numeric($params['pagesize']) ? (int)$params['pagesize'] : 10;
            $start = ($page-1) * $pagesize;
            $field = "a.*,b.brand,c.product_name,d.username create_username,e.username charge_username";
            $sql = "SELECT {$field} FROM task a 
                    LEFT JOIN brand b ON a.brand_id = b.id 
                    LEFT JOIN product c ON a.product_id = c.id
                    LEFT JOIN enum_user d ON a.create_userid = d.userid 
                    LEFT JOIN enum_user e ON a.charge_userid = e.userid 
                    WHERE is_delete = 0 AND (a.create_userid = '{$userid}' OR a.charge_departmentId = '{$departmentId}') ";
            
            // 发起人
            if(!empty($params['create_userid'])){
                $sql .= "AND a.create_userid = '{$params['create_userid']}' ";
            }
            // 类型
            if(!empty($params['type'])){
                $sql .= "AND a.tid IN (SELECT tid FROM task_list WHERE `type` = '{$params['type']}') ";    
            }
            // 状态
            if(!empty($params['status']) && is_numeric($params['status'])){
                $sql .= "AND a.status = {$params['status']} ";
            }
            // 优先级
            if(!empty($params['important']) && is_numeric($params['important'])){
                $sql .= "AND a.important = {$params['important']} ";
            }
            // 期望完成日期
            if(!empty($params['finish_date_start']) && !empty($params['finish_date_end'])){
                $sql .= "AND a.finish_date BETWEEN '{$params['finish_date_start']}' AND '{$params['finish_date_end']}' ";
            }
            // 客户ID
            if(!empty($params['customer_id'])){
                $sql .= "AND a.brand_id IN (SELECT id FROM brand WHERE `customer_id` = '{$params['customer_id']}') ";    
            }
            // 品牌ID
            if(!empty($params['brand_id'])  && is_numeric($params['brand_id'])){
                $sql .= "AND a.brand_id = {$params['brand_id']} ";
            }
            // 产品ID
            if(!empty($params['product_id'])){
                $sql .= "AND a.product_id = {$params['product_id']} ";
            }
            //需求名称
            if(!empty($params['exact'])){
                $sql .= "AND a.name LIKE '%{$params['exact']}%' ";
            }
            $order = "ORDER BY a.create_time DESC LIMIT {$start},{$pagesize}";
            $csql = str_replace($field,'count(*)',$sql);
            $sql .= $order;
            $res = $this->getRes($sql);
            if(!empty($res)){
                $res = array_map(function($v){
                    $brandModel = new BrandModel;
                    $v['tasklist'] = $this->get_task_list($v['tid'],'tid,type,total');
                    $v['filelist'] = $this->get_task_file($v['tid'],'name,size,format,url');
                    $customer = $brandModel->findBrandCustomerById($v['brand_id']);
                    $v['customer_name'] = !empty($customer['customer_name']) ? $customer['customer_name'] : '';
                    $v['uploaded'] = $this->get_task_uploaded_by_tid($v['tid']);
                    if (!empty($v['tasklist'])) {
                        $total = array_reduce($v['tasklist'], function($carry, $item) {
                            return $carry + (isset($item['total']) ? $item['total'] : 0);
                        }, 0);
                        $v['total'] = $total;
                        $v['schedule'] = round($this->get_task_uploaded_by_tid($v['tid'])/$total,2)*100;
                    }else {
                        $v['total'] = 0;
                        $v['schedule'] = 0;
                    }
                    return $v;
                },$res);
            }
            $count = $this->getCount($csql);
            return $this->pageData($res,$pagesize,$page,$count);
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }
    }

    /**
     * 任务详情
     * @param string $type
     * @param string $tid
     * 
     * @return array
     */
    public function getInfo($userid, $departmentId, $params)
    {
        try {
            $tid = $params['tid'];
            switch ($params['type']) {
                case 'baseinfo':
                    $taskinfo = Db::table('task')
                                ->field('a.*,b.brand,b.team_video,c.product_name,d.username create_username,d.department create_department,e.username charge_username,e.department charge_department')
                                ->alias('a')
                                ->Join('brand b','a.brand_id = b.id','left')
                                ->Join('product c','a.product_id = c.id','left')
                                ->Join('enum_user d','a.create_userid = d.userid','left')    
                                ->Join('enum_user e','a.charge_userid = e.userid','left')
                                ->where(['a.tid' => $tid])
                                ->find();
                    $customer = Db::table('brand')
                                ->alias('a')
                                ->join('enum_customer b','a.customer_id = b.customer_id')
                                ->where(['a.id' => $taskinfo['brand_id']])
                                ->find();
                    $taskinfo['customer_name'] = !empty($customer['customer_name']) ? $customer['customer_name'] : '';
                    $taskinfo['days'] = floor((time() - strtotime($taskinfo['create_time'])) / (60 * 60 * 24));
                    $taskinfo['tasklist'] = Db::table('task_list')
                                            ->where(['tid' => $tid])
                                            ->select();
                    if(!empty($taskinfo['tasklist'])){
                        $total = 0;
                        foreach ($taskinfo['tasklist'] as $key => $value) {
                            $total += $value['total'];
                            $taskinfo['tasklist'][$key]['uploaded'] = $this->get_task_uploaded_by_tid_and_type($tid,$value['type']);    // 已上传数量
                            $taskinfo['tasklist'][$key]['schedule'] = round($taskinfo['tasklist'][$key]['uploaded']/$value['total'],2)*100;    // 进度
                        }
                        $taskinfo['schedule'] = round($this->get_task_uploaded_by_tid($tid)/$total,2)*100;
                    }else {
                        $taskinfo['schedule'] = 0;    
                    }
                    $taskinfo['filelist'] = Db::table('task_file')
                                            ->where(['tid' => $tid])
                                            ->select();
                    $taskinfo['latest_log'] = Db::table('task_log')->where(['tid' => $tid])->order('create_time desc')->find();
                    return $taskinfo; 
                case 'loglist':
                    $page       = isset($params['page']) && is_numeric($params['page']) ? (int)$params['page'] : 1;
                    $pagesize   = isset($params['pagesize']) && is_numeric($params['pagesize']) ? (int)$params['pagesize'] : 10;
                    $start      = ($page-1) * $pagesize;
                    $field      = "a.*,b.username";
                    $sql = "SELECT
                                {$field} 
                            FROM
                                task_log a
                                LEFT JOIN enum_user b ON a.userid = b.userid 
                            WHERE
                                a.tid = '{$tid}' ";
                    $order  = "ORDER BY a.create_time DESC LIMIT {$start},{$pagesize}";
                    $csql   = str_replace($field,'count(*)',$sql);
                    $sql    .= $order;
                    $res    = $this->getRes($sql);
                    $count  = $this->getCount($csql);
                    return $this->pageData($res,$pagesize,$page,$count);
                default:
                    throw new \Exception('type参数错误');
                    break;
            }
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }
    }

    /**
     * 操作任务
     * @param string $userid
     * @param string $departmentId
     * @param array $params
     */
    public function handleTask($userid, $departmentId, $params)
    {
        try {
            $tid    = $params['tid'];
            $method = $params['method'];
            $taskinfo = $this->get_task_info_by_tid($tid);
            if(empty($taskinfo)) throw new \Exception(ApiErrMsg::TASK_NOT_EXIST);
            if ($userid !== $taskinfo['create_userid'] && $userid !== $taskinfo['charge_userid']) {
                throw new \Exception(ApiErrMsg::NO_HANDLE_AUTH);
            }
            $emailObj = new \app\common\tool\Email;
            $charge_userinfo = ['userid' => $taskinfo['charge_userid'],'username' => $taskinfo['charge_username'],'email' => $taskinfo['charge_email']];
            $create_userinfo = ['userid' => $taskinfo['create_userid'],'username' => $taskinfo['create_username'],'email' => $taskinfo['create_email']];
            if (empty($charge_userinfo['email']) || empty($create_userinfo['email'])) {
                throw new \Exception(ApiErrMsg::NO_SEND_EMAIL);
            }
            switch ($method) {
                case 'ACCEPT':      // 接受
                    // 发送通知邮件 TASK_IS_IN_PRODUCTION 任务生产中
                    // 修改状态为制作中 2
                    if ($taskinfo['status'] !== 1) {
                        throw new \Exception(ApiErrMsg::STATUS_FORBIDDEN_HANDLE);
                    }
                    if ($taskinfo['charge_userid'] !== $userid) {
                        throw new \Exception(ApiErrMsg::NO_HANDLE_AUTH);    
                    }
                    $info = [
                        'to_email'          => $create_userinfo['email'],
                        'to_username'       => $create_userinfo['username'],
                        'task_name'         => $taskinfo['name'],
                        'create_username'   => $create_userinfo['username'],
                        'charge_username'   => $charge_userinfo['username'],
                        'accept_time'       => date('Y-m-d H:i:s'),
                    ];
                    $result = $emailObj->handle_send('TASK_IS_IN_PRODUCTION', $info);
                    if($result){
                        $this->update_task_status($tid, 2);
                        $this->write_task_log($tid, $userid, '接受需求：'."【".$taskinfo['name']."】".'，进入制作中');
                        Db::commit();
                        return true;
                    }
                    Db::rollback();
                    throw new \Exception(ApiErrMsg::SEND_FAIL);
                case 'REJECT':      // 拒绝
                    // 发送通知邮件 TASK_IS_REFUSED 任务已拒绝
                    // 修改状态为制作中 5
                    if ($taskinfo['status'] !== 1) {
                        throw new \Exception(ApiErrMsg::STATUS_FORBIDDEN_HANDLE);
                    }
                    if ($taskinfo['charge_userid'] !== $userid) {
                        throw new \Exception(ApiErrMsg::NO_HANDLE_AUTH);    
                    }
                    $info = [
                        'to_email'          => $create_userinfo['email'],
                        'to_username'       => $create_userinfo['username'],
                        'task_name'         => $taskinfo['name'],
                        'charge_username'   => $charge_userinfo['username'],
                        'refuse_time'       => date('Y-m-d H:i:s'),
                        'reason'            => $params['reason'],
                    ];
                    $result = $emailObj->handle_send('TASK_IS_REFUSED', $info);
                    if($result){
                        $this->update_task_status($tid, 5);
                        $this->write_task_log($tid, $userid, '拒绝需求理由：'."【".$params['reason']."】");
                        Db::commit();
                        return true;
                    }
                    Db::rollback();
                    throw new \Exception(ApiErrMsg::SEND_FAIL);
                case 'ROLLBACK':    // 回退
                    // 区分是回退至发起人还是负责人
                    // 发送通知邮件 ROLLBACK_TO_CREATE_USER 或者 ROLLBACK_TO_CHARGE_USER 任务已回退
                    // 修改状态为制作中 3
                    if (!in_array($taskinfo['status'],[1,2,4])) {   // 仅 待接受1 制作中2 待审核4 可回退
                        throw new \Exception(ApiErrMsg::STATUS_FORBIDDEN_HANDLE);
                    }
                    if ($taskinfo['charge_userid'] !== $userid && $taskinfo['create_userid'] !== $userid) {
                        throw new \Exception(ApiErrMsg::NO_HANDLE_AUTH);    
                    }
                    if ($userid == $charge_userinfo['userid']) {
                        // 回退至发起人 ROLLBACK_TO_CREATE_USER
                        $info = [
                            'to_email'          => $create_userinfo['email'],
                            'to_username'       => $create_userinfo['username'],
                            'task_name'         => $taskinfo['name'],
                            'charge_username'   => $charge_userinfo['username'],
                            'rollback_time'     => date('Y-m-d H:i:s'),
                            'reason'            => $params['reason'],
                        ];
                        $result = $emailObj->handle_send('ROLLBACK_TO_CREATE_USER', $info);
                        if($result){
                            $this->update_task_status($tid, 3);
                            $this->write_task_log($tid, $userid, '回退需求理由：'."【".$params['reason']."】");
                            Db::commit();
                            return true;
                        }
                        Db::rollback();
                        throw new \Exception(ApiErrMsg::SEND_FAIL);
                    }elseif ($userid == $create_userinfo['userid']) {
                        // 回退至负责人 ROLLBACK_TO_CHARGE_USER
                        $info = [
                            'to_email'          => $create_userinfo['email'],
                            'to_username'       => $create_userinfo['username'],
                            'task_name'         => $taskinfo['name'],
                            'create_username'   => $create_userinfo['username'],
                            'charge_username'   => $charge_userinfo['username'],
                            'rollback_time'     => date('Y-m-d H:i:s'),
                            'reason'            => $params['reason'],
                        ];
                        $result = $emailObj->handle_send('ROLLBACK_TO_CHARGE_USER', $info);
                        if($result){
                            $this->update_task_status($tid, 3);
                            $this->write_task_log($tid, $userid, '回退需求理由：'."【".$params['reason']."】");
                            Db::commit();
                            return true;
                        }
                        Db::rollback();
                        throw new \Exception(ApiErrMsg::SEND_FAIL);
                    }else {
                        throw new \Exception(ApiErrMsg::NO_HANDLE_AUTH);
                    }
                    break;
                case 'CONFIRM':     // 确认通过
                    // 发送通知邮件 TASK_IS_COMPLETED 任务已完成
                    // 修改状态为制作中 6
                    if ($taskinfo['status'] !== 4) {
                        throw new \Exception(ApiErrMsg::STATUS_FORBIDDEN_HANDLE);
                    }
                    if ($taskinfo['create_userid'] !== $userid) {
                        throw new \Exception(ApiErrMsg::NO_HANDLE_AUTH);    
                    }
                    $info = [
                        'to_email'          => $charge_userinfo['email'],
                        'to_username'       => $charge_userinfo['username'],
                        'task_name'         => $taskinfo['name'],
                        'create_username'   => $create_userinfo['username'],
                    ];
                    $result = $emailObj->handle_send('TASK_IS_COMPLETED', $info);
                    if($result){
                        $this->update_task_status($tid, 6);
                        $this->write_task_log($tid, $userid, '确认需求完成');
                        Db::commit();
                        return true;
                    }
                    Db::rollback();
                    throw new \Exception(ApiErrMsg::SEND_FAIL);
                    break;
                case 'RELAUNCH':    // 重新发起
                    // 仅在已回退状态下可以重新发起
                    // 区分是发起人重新发起还是负责人重新发起,发起人重新发起状态为待接受，负责人重新发起状态为待审核
                    if ($taskinfo['status'] != 3) {
                        throw new \Exception(ApiErrMsg::STATUS_FORBIDDEN_HANDLE);
                    }
                    if ($taskinfo['charge_userid'] !== $userid && $taskinfo['create_userid'] !== $userid) {
                        throw new \Exception(ApiErrMsg::NO_HANDLE_AUTH);    
                    }
                    if ($userid == $charge_userinfo['userid']) {
                        // 负责人重新发起
                        // 状态修改为待审核 4
                        // 发送邮件给发起人
                        $info = [
                            'to_email'          => $create_userinfo['email'],
                            'to_username'       => $create_userinfo['username'],
                            'task_name'         => $taskinfo['name'],
                            'charge_username'   => $charge_userinfo['username'],
                            'submit_time'       => date('Y-m-d H:i:s'),
                        ];
                        $result = $emailObj->handle_send('TASK_IS_UNDER_REVIEW', $info);
                        if($result){
                            $this->update_task_status($tid, 4);
                            $this->write_task_log($tid, $userid, '负责人重新发起需求，待审核');
                            Db::commit();
                            return true;
                        }
                        Db::rollback();
                        throw new \Exception(ApiErrMsg::SEND_FAIL);
                    }elseif ($userid == $create_userinfo['userid']) {
                        // 发起人重新发起
                        // 状态修改为待接受 1
                        // 发送邮件给负责人
                        $info = [
                            'to_email'          => $charge_userinfo['email'],
                            'to_username'       => $charge_userinfo['username'],
                            'task_name'         => $taskinfo['name'],
                            'create_username'   => $create_userinfo['username'],
                            'create_time'       => date('Y-m-d H:i:s'),
                        ];
                        $result = $emailObj->handle_send('TASK_IS_WAITING_ACCEPT', $info);
                        if($result){
                            $this->update_task_status($tid, 1);
                            $this->write_task_log($tid, $userid, '发起人重新发起需求，待接受');
                            Db::commit();
                            return true;
                        }
                        Db::rollback();
                        throw new \Exception(ApiErrMsg::SEND_FAIL);
                    }else {
                        throw new \Exception(ApiErrMsg::NO_HANDLE_AUTH);      
                    }
                    break;
                case 'EDITINFO':    //编辑
                    if ($taskinfo['status'] != 1 && $taskinfo['status'] != 3) {
                        throw new \Exception(ApiErrMsg::STATUS_FORBIDDEN_HANDLE);
                    }
                    if ($taskinfo['create_userid'] !== $userid) {
                        throw new \Exception(ApiErrMsg::NO_HANDLE_AUTH);    
                    }
                    $desc = '';
                    $info = [];
                    if ($taskinfo['name'] !== $params['name']) {
                        $info['name'] = $params['name'];
                        $desc .= '需求名称由【'.$taskinfo['name'].'】改为【'.$params['name'].'】，';     
                    }
                    $up_task_list_flag = false;
                    $old_task_list = $this->get_task_list($tid,'type,total');
                    $new_task_list = $params['tasklist'];
                    if (!arraysAreEqual($old_task_list,$new_task_list)) {
                        $up_task_list_flag = true;
                        $old_name_string = implode(',', array_column($old_task_list,'type'));
                        $new_name_string = implode(',', array_column($new_task_list,'type'));
                        $desc .= '需求清单由【' . $old_name_string . '】改为【' . $new_name_string . '】，';     
                    }
                    if ($taskinfo['finish_date'] !== $params['finish_date']) {
                        $info['finish_date'] = $params['finish_date'];
                        $desc .= '期望日期由【'.$taskinfo['finish_date'].'】改为【'.$params['finish_date'].'】，';
                    }
                    if ($taskinfo['important'] !== $params['important']) {
                        $info['important'] = $params['important'];
                        $old_important_desc = \app\common\tool\Enum::IMPORTANT[$taskinfo['important']];
                        $new_important_desc = \app\common\tool\Enum::IMPORTANT[$params['important']];
                        $desc .= '优先级由【'.$old_important_desc.'】改为【'.$new_important_desc.'】，';
                    }
                    if ($taskinfo['brand_id'] !== $params['brand_id']) {
                        $info['brand_id'] = $params['brand_id'];
                        $old_brand_info = Db::name('brand')->where('id', $taskinfo['brand_id'])->find();
                        $new_brand_info = Db::name('brand')->where('id', $params['brand_id'])->find();
                        $desc .= '品牌由【'.$old_brand_info['brand'].'】改为【'.$new_brand_info['brand'].'】，';
                    }
                    if ($taskinfo['product_id'] !== $params['product_id']) {
                        $info['product_id'] = $params['product_id'];
                        $old_product_info = Db::name('product')->where('id', $taskinfo['product_id'])->find();
                        $new_product_info = Db::name('product')->where('id', $params['product_id'])->find();
                        $desc .= '产品由【'.$old_product_info['product_name'].'】改为【'.$new_product_info['product_name'].'】，';
                    }
                    if ($taskinfo['charge_userid'] !== $params['charge_userid']) {
                        $info['charge_userid'] = $params['charge_userid'];
                        $old_charge_userinfo = Db::name('enum_user')->where('userid', $taskinfo['charge_userid'])->find();
                        $new_charge_userinfo = Db::name('enum_user')->where('userid', $params['charge_userid'])->find();
                        $desc .= '负责人由【'.$old_charge_userinfo['username'].'】改为【'.$new_charge_userinfo['username'].'】，';
                    }
                    $up_task_file_flag = false;
                    $old_task_file = $this->get_task_file($tid,'name,size,format,url');
                    $new_task_file = $params['filelist'];
                    if (!arraysAreEqual($old_task_file,$new_task_file)) {
                        $up_task_file_flag = true;
                        $old_file_string = implode(',', array_column($old_task_file,'name'));
                        $new_file_string = implode(',', array_column($new_task_file,'name'));
                        $desc .= '附件由【' . $old_file_string . '】改为【' . $new_file_string . '】，';     
                    }
                    
                    $desc = rtrim($desc, "，");
                    
                    try {
                        Db::startTrans();
                        if ($up_task_list_flag) {
                            $this->delete_task_list($tid);        
                            $this->insert_task_list($tid,$params['tasklist']);        
                        }
                        if ($up_task_file_flag) {
                            $this->delete_task_file($tid);
                            $this->insert_task_file($tid,$params['filelist']);
                        }
                        $this->update_task_info($tid, $info);
                        $this->write_task_log($tid, $userid, '编辑需求信息：'.$desc);
                        Db::commit();
                        return true;
                    } catch (\Exception $e) {
                        Db::rollback();
                        return false;
                    }
                    break;
                default:
                    throw new \Exception(ApiErrMsg::PARAM_ERROR);
                    break;
            }
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }   
    }

    public function update_task_info($tid, $info)
    {
        if (empty($info)) {
            return true;
        }
        return Db::name('task')->where('tid', $tid)->update($info);
    }

    public function get_task_list($tid, $field = '*')
    {
        return Db::name('task_list')->field($field)->where('tid', $tid)->select();
    }

    protected function get_task_file($tid, $field = '*')
    {
        return Db::name('task_file')->field($field)->where('tid', $tid)->select();
    }

    protected function delete_task_list($tid)
    {
        return Db::name('task_list')->where('tid', $tid)->delete();
    }

    protected function insert_task_list($tid,$tasklist)
    {
        $data = [];
        foreach ($tasklist as $key => $value) {
            $data[$key]['tid']      = $tid;    
            $data[$key]['type']     = $value['type'];    
            $data[$key]['total']    = $value['total'];    
        }
        return Db::name('task_list')->insertAll($data);
    }

    protected function delete_task_file($tid)
    {
        return Db::name('task_file')->where('tid', $tid)->delete();
    }

    protected function insert_task_file($tid,$filelist)
    {
        $data = [];
        foreach ($filelist as $key => $value) {
            $data[$key]['tid']      = $tid;    
            $data[$key]['name']     = $value['name'];    
            $data[$key]['size']     = $value['size'];    
            $data[$key]['format']   = $value['format'];    
            $data[$key]['url']      = $value['url'];    
        }
        return Db::name('task_file')->insertAll($data);    
    }

    protected function get_task_uploaded_by_tid_and_type($tid,$type)
    {
        return Db::name('video')->where('tid', $tid)->where('type', $type)->count();    
    }

    public function get_task_uploaded_by_tid($tid)
    {
        return Db::name('video')->where('tid', $tid)->count();    
    }

    public function get_task_info_by_tid($tid)
    {
        return Db::table('task')
                ->field('a.*,b.brand,b.team_video,c.product_name,d.username create_username,d.email create_email,d.department create_department,
                            e.username charge_username,e.email charge_email,e.department charge_department')
                ->alias('a')
                ->Join('brand b','a.brand_id = b.id','left')
                ->Join('product c','a.product_id = c.id','left')
                ->Join('enum_user d','a.create_userid = d.userid','left')    
                ->Join('enum_user e','a.charge_userid = e.userid','left')
                ->where(['a.tid' => $tid])
                ->find();    
    }

    public function getOverview($userid, $departmentId)
    {
        try {
            $field = "a.*,b.brand,c.product_name,d.username create_username,e.username charge_username";
            $sql = "SELECT {$field} FROM task a 
                    LEFT JOIN brand b ON a.brand_id = b.id 
                    LEFT JOIN product c ON a.product_id = c.id
                    LEFT JOIN enum_user d ON a.create_userid = d.userid 
                    LEFT JOIN enum_user e ON a.charge_userid = e.userid 
                    WHERE is_delete = 0 AND (a.create_userid = '{$userid}' OR a.charge_departmentId = '{$departmentId}') ";
            $res = Db::query($sql);
            if (!empty($res)) {
                $res = array_map(function($v){
                    $v['tasklist'] = Db::table('task_list')->field('tid,type,total')->where(['tid' => $v['tid']])->select();
                    $customer = Db::table('brand')
                                ->alias('a')
                                ->join('enum_customer b','a.customer_id = b.customer_id')
                                ->where(['a.id' => $v['brand_id']])
                                ->find();
                    $v['customer_name'] = !empty($customer['customer_name']) ? $customer['customer_name'] : '';
                    $v['schedule'] = 0;
                    return $v;
                },$res);
                $grouped = [];
                foreach ($res as $item) {
                    $status = $item['status'];
                    if (!isset($grouped[$status])) {
                        $grouped[$status]['status'] = $status;
                        $grouped[$status]['list'][] = $item;
                    }else {
                        $grouped[$status]['list'][] = $item;
                    }
                }
                return array_values($grouped);   
            }
            return [];
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }    
    }

    public function deleteTask($userid,$departmentId,$params)
    {
        try {
            $tid    = $params['tid'];
            $taskinfo = $this->get_task_by_tid($tid);
            if (empty($taskinfo)) {
                return false;
            }
            if ($userid !== $taskinfo['create_userid']) {
                throw new \Exception(ApiErrMsg::NO_HANDLE_AUTH);
            }
            if (!in_array($taskinfo['status'],[1,3])) {
                throw new \Exception(ApiErrMsg::STATUS_FORBIDDEN_HANDLE);
            }
            $res = Db::table('task')->where(['tid' => $tid])->update(['is_delete' => 1]);
            $this->write_task_log($tid, $userid, "删除需求：【".$taskinfo['name']."】"); 
            if ($res) {
                return true;
            }
            return false;
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }
    }
}